package com.example.springmvc.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;

import com.example.springmvc.domain.InstructorInfo;

@Service("instructorDao")
public class InstructorDaoImpl implements DaoBase {
	
	private DataSource dataSource;
	
	@Autowired
	public void setDataSource(DataSource dataSource){
		this.dataSource = dataSource;
	}
	
	public List selectAll() throws SQLException {

		String sql = "SELECT * FROM INSTRUCTOR_INFO";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		
		List<InstructorInfo> results = new ArrayList<InstructorInfo>();
		List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
		
		for(Map<String, Object> row : rows){
			InstructorInfo instructorInfo = new InstructorInfo();
			instructorInfo.setInstructor_id((String) row.get("INSTRUCTOR_ID"));
			instructorInfo.setName((String) row.get("NAME"));
			instructorInfo.setPersonal_id((String) row.get("PERSONNEL_ID"));
			instructorInfo.setSchool_name((String) row.get("SCHOOL_NAME"));
			instructorInfo.setMajor((String) row.get("MAJOR"));
			instructorInfo.setPhone_num((String) row.get("PHONE_NUM"));
			instructorInfo.setEmail((String) row.get("EMAIL"));
			instructorInfo.setAddress((String) row.get("ADDRESS"));
			
			results.add(instructorInfo);
		}
		
		return results;
	}

	public Object selectById(String id) throws SQLException {
		String sql = "SELECT * FROM INSTRUCTOR_INFO WHERE INSTRUCTOR_ID=?";
		
		final InstructorInfo instructorInfo = new InstructorInfo();
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.query(sql, new Object[]{ id },
				new RowCallbackHandler(){

					public void processRow(ResultSet rs) throws SQLException {
						// Mapping
						instructorInfo.setInstructor_id(rs.getString("INSTRUCTOR_ID"));
						instructorInfo.setId(rs.getString("ID"));
						instructorInfo.setPasswd(rs.getString("PASSWD"));
						instructorInfo.setName(rs.getString("NAME"));
						instructorInfo.setPersonal_id(rs.getString("PERSONNEL_ID"));
						instructorInfo.setSchool_name(rs.getString("SCHOOL_NAME"));
						instructorInfo.setMajor(rs.getString("MAJOR"));
						instructorInfo.setPhone_num(rs.getString("PHONE_NUM"));
						instructorInfo.setEmail(rs.getString("EMAIL"));
						instructorInfo.setAddress(rs.getString("ADDRESS"));
						
					}
		
				});
		return (Object)instructorInfo;
	}

	public boolean update(Object o) throws SQLException {
		InstructorInfo instructorInfo = (InstructorInfo)o;
		String sql = "UPDATE INSTRUCTOR_INFO " +
				"SET ID = ?, PASSWD = ?, NAME = ?, PERSONNEL_ID = ?, SCHOOL_NAME = ?, " +
				"MAJOR = ?, PHONE_NUM = ?, EMAIL = ?, ADDRESS = ? " +
				"WHERE INSTRUCTOR_ID = ?";
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql, new Object[] {instructorInfo.getId(),
											instructorInfo.getPasswd(),
											instructorInfo.getName(),
											instructorInfo.getPersonal_id(),
											instructorInfo.getSchool_name(),
											instructorInfo.getMajor(),
											instructorInfo.getPhone_num(),
											instructorInfo.getEmail(),
											instructorInfo.getAddress(),
											instructorInfo.getInstructor_id()});
		
		return true;
	}

	public boolean delete(String id) throws SQLException {
		String sql = "DELETE FROM INSTRUCTOR_INFO " +
				"WHERE INSTRUCTOR_ID = ?";
	
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql, new Object[] { id });
		
		return true;
	}

	public boolean insert(Object o) throws SQLException {
		InstructorInfo instructorInfo = (InstructorInfo)o;
		String sql = "INSERT INTO INSTRUCTOR_INFO(INSTRUCTOR_ID, ID, PASSWD, NAME, " +
				"PERSONNEL_ID, SCHOOL_NAME, MAJOR, PHONE_NUM, EMAIL, ADDRESS) " +
				"VALUES(EMPLOYEES_SEQ.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql, new Object[] {instructorInfo.getId(),
											instructorInfo.getPasswd(),
											instructorInfo.getName(),
											instructorInfo.getPersonal_id(),
											instructorInfo.getSchool_name(),
											instructorInfo.getMajor(),
											instructorInfo.getPhone_num(),
											instructorInfo.getEmail(),
											instructorInfo.getAddress()});
		
		return false;
	}

	public String checkLogIn(String id, String passwd) {
		// TODO Auto-generated method stub
		return null;
	}

}
